DROP DATABASE IF EXISTS EasyTimeTracker;
CREATE DATABASE EasyTimeTracker;

CREATE USER IF NOT EXISTS 'easytt'@'localhost'
identified by '6rulleMuck!34';

GRANT ALL ON EasyTimeTracker.* TO 'easytt'@'localhost';

use EasyTimeTracker;


CREATE TABLE IF NOT EXISTS customer
(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name char(50) NOT NULL,
    street char(50) NOT NULL,
    nr INT NOT NULL,
    postcode CHAR(12) NOT NULL,
    city char(30) NOT NULL,
    created datetime NOT NULL,
    retired datetime,
    CONSTRAINT uc_customer UNIQUE(name)
);




CREATE TABLE IF NOT EXISTS project
(
    id int not null AUTO_INCREMENT PRIMARY KEY,
    name char(50) NOT NULL,
    customer_id int NOT NULL,
    created datetime not null,
    retired datetime,
    CONSTRAINT fk_project_customer FOREIGN KEY(customer_id) REFERENCES customer(id),
    CONSTRAINT uc_project UNIQUE(name, customer_id)
);





CREATE TABLE IF NOT EXISTS task
(
    id int not null AUTO_INCREMENT PRIMARY KEY,
    name char(50) NOT NULL,
    project_id int NOT NULL,
    created datetime not null,
    retired datetime,
    CONSTRAINT fk_task_project FOREIGN KEY(project_id) REFERENCES project(id),
    CONSTRAINT uc_task UNIQUE(name, project_id)
);



CREATE TABLE IF NOT EXISTS user
( 
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name char(30) NOT NULL ,
    # email char(100) NOT NULL,
    pass char(128) NOT NULL,
    last_task int,
    level int,
    last_login datetime,
    created datetime NOT NULL,
    CONSTRAINT fk_user_task FOREIGN KEY(last_task) REFERENCES task(id),
    CONSTRAINT uc_user UNIQUE(name)
);
# user.level legend -> config.php

# pass is sha512 hash of "admin"
INSERT INTO user (id, name, pass, level, last_task, created) values(1, 'admin', 'c7ad44cbad762a5da0a452f9e854fdc1e0e7a52a38015f23f3eab1d80b931dd472634dfac71cd34ebc35d16ab7fb8a90c81f975113d6c7538dc69dd8de9077ec', 100, 1, NOW());

CREATE TABLE IF NOT EXISTS connection_log(
    ip varchar(45) NOT NULL PRIMARY KEY, # ip v6 ready
    last_seen datetime NOT NULL,
    last_infraction datetime NULL,
    failedattempts int NOT NULL
);

CREATE TABLE IF NOT EXISTS time
(
    id int not null AUTO_INCREMENT PRIMARY KEY,
    start datetime NOT NULL,
    stop datetime NOT NULL,
    task_id int not null,
    user_id int not null,
    CONSTRAINT fk_time_user FOREIGN KEY(user_id) REFERENCES user(id),
    CONSTRAINT fk_time_task FOREIGN KEY(task_id) REFERENCES task(id),
    text varchar(200),
    billdate date
);
# test data

